﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using SCRUM.Core.DAO;
using System.Data.SqlClient;
using SCRUM.Core.Exceptions.Database;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database for sprint
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {
        /// <summary>
        /// Creates new sprint.
        /// </summary>
        /// <param name="sprint">The specific sprint.</param>
        /// <param name="releaseId">Release id to add sprint to.</param>
        /// <returns>Created sprint</returns>
        public override Sprint CreateSprint(Sprint sprint, int releaseId)
        {
            int sprintID  = InsertSprint(sprint, releaseId);
            return GetSprintById(sprintID);
        }

        /// <summary>
        /// Inserts the sprint into database.
        /// </summary>
        /// <param name="sprint">The sprint.</param>
        /// <param name="releaseId">The release id.</param>
        /// <returns>identifier of newly created sprint</returns>
        private int InsertSprint(Sprint sprint, int releaseId)
        {
            try
            {
                DbParameter dbName = CreateParameter("name", sprint.Name);
                DbParameter dbDesc = CreateParameter("description", sprint.Description);
                DbParameter dbFrom = CreateParameter("fromDate", sprint.FromDate);
                DbParameter dbTo = CreateParameter("toDate", sprint.ToDate);
                DbParameter dbState = CreateParameter("sprintStateId", sprint.State.ID);
                DbParameter dbFuncEst = CreateParameter("funcEst", sprint.FunctionalEstimation);
                DbParameter dbTechEst = CreateParameter("techEst", sprint.TechnicalEstimation);
                DbParameter dbOthrEst = CreateParameter("othrEst", sprint.OtherEstimation);
                DbParameter dbReleaseId = CreateParameter("releaseId", sprint.ReleaseID);
                DbParameter dbEst = CreateParameter("estimation", sprint.Estimation);

                connection.Open();

                int data = ExecuteNonQuery("sprint/CreateSprint", dbName, dbDesc, dbFrom, dbTo, dbReleaseId, dbState, dbFuncEst, dbTechEst, dbOthrEst, dbEst);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("SprintCouldNotBeCreated");
                }
                return Convert.ToInt32(GetScalar("sprint/GetLastInsertedSprintId"));
            }
            catch(SqlException e)
            {
                throw new InconsistentDatabaseException("SprintCouldNotBeCreated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates the sprint.
        /// </summary>
        /// <param name="sprint">The specific sprint.</param>
        /// <param name="releaseId">id of release that sprint is in</param>
        public override void UpdateSprint(Sprint sprint, int releaseId)
        {
            try
            {
                DbParameter dbId = CreateParameter("sprintId", sprint.ID);
                DbParameter dbName = CreateParameter("name", sprint.Name);
                DbParameter dbDesc = CreateParameter("description", sprint.Description);
                DbParameter dbFrom = CreateParameter("fromDate", sprint.FromDate);
                DbParameter dbTo = CreateParameter("toDate", sprint.ToDate);
                DbParameter dbState = CreateParameter("sprintStateId", sprint.State.ID);
                DbParameter dbFuncEst = CreateParameter("funcEst", sprint.FunctionalEstimation);
                DbParameter dbTechEst = CreateParameter("techEst", sprint.TechnicalEstimation);
                DbParameter dbOthrEst = CreateParameter("othrEst", sprint.OtherEstimation);
                DbParameter dbReleaseId = CreateParameter("releaseId", sprint.ReleaseID);
                DbParameter dbEst = CreateParameter("estimation", sprint.Estimation);

                connection.Open();

                int data = ExecuteNonQuery("sprint/UpdateSprint", dbId, dbName, dbDesc, dbFrom, dbTo, dbState, dbReleaseId, dbFuncEst, dbTechEst, dbOthrEst, dbEst);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("SprintToBeUpdatedNotFound");
                }
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("SprintCouldNotBeUpdated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the sprint.
        /// </summary>
        /// <param name="sprintId">The specific sprint id.</param>
        public override void DeleteSprint(int sprintId)
        {

            try
            {
                DbParameter dbId = CreateParameter("sprintId", sprintId);

                connection.Open();

                int data = ExecuteNonQuery("sprint/DeleteSprint", dbId);
                if (data != 1)
                {
                    throw new Exception("SprintToBeDeletedNotFound");
                }
            }
            catch (SqlException e)
            {
                throw new InconsistentDatabaseException("SprintCouldNotBeDeleted");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets the sprint.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns>Created sprint</returns>
        private Sprint GetSprint(DbDataReader data)
        {
            return new Sprint(
                GetIntByColumn(data, "ID")
                , GetStringByColumn(data, "NAME")
                , GetStringByColumn(data, "DESCRIPTION")
                , GetIntByColumn(data, "STATE_ID")
                , GetDateByColumn(data, "FROM_DATE")
                , GetDateByColumn(data, "TO_DATE")
                , GetDoubleByColumn(data, "ESTIMATION")
                , GetDoubleByColumn(data, "PROGRESS")
                , GetDateByColumn(data, "CHANGED")
                , GetIntByColumn(data, "FUNC_EST")
                , GetIntByColumn(data, "TECH_EST")
                , GetIntByColumn(data, "OTHR_EST")
                , GetIntByColumn(data, "RELEASE_ID")
            );
        }

        /// <summary>
        /// Gets all sprints.
        /// </summary>
        /// <returns>Sprints</returns>
        public override IList<Sprint> GetAllSprints(int projectID, int releaseID, int sprintID, int userStoryID)
        {
            try
            {
                IList<Sprint> sprints = new List<Sprint>();

                DbParameter dbProjectID = CreateParameter("projectID", projectID);
                DbParameter dbReleaseID = CreateParameter("releaseID", releaseID);
                DbParameter dbSprintID = CreateParameter("sprintID", sprintID);
                DbParameter dbUserStoryID = CreateParameter("userStoryID", userStoryID);

                connection.Open();

                DbDataReader data = GetGrid("sprint/GetAllSprints", dbProjectID, dbReleaseID, dbSprintID, dbUserStoryID);
                while (data.Read())
                {
                    Sprint sprint = GetSprint(data);
                    sprints.Add(sprint);
                }
                return sprints;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets all sprints from future or all (acodring to parameter future)
        /// </summary>
        /// <param name="future">if set to <c>true</c> [future].</param>
        /// <returns>All sprints</returns>
        protected IList<Sprint> GetAllSprints(bool future)
        {
            try
            {
                IList<Sprint> sprints = new List<Sprint>();
                DbParameter dbFuture = CreateParameter("future", future);

                connection.Open();

                DbDataReader data = GetGrid("sprint/GetAllFutureSprints", dbFuture);
                while (data.Read())
                {
                    Sprint sprint = GetSprint(data);
                    sprints.Add(sprint);
                }
                return sprints;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets all sprints.
        /// </summary>
        /// <returns>List of Sprints</returns>
        public override IList<Sprint> GetAllSprints()
        {
            return GetAllSprints(false);
        }

        /// <summary>
        /// Gets all future sprints.
        /// </summary>
        /// <returns>List of all task, which did not ended.</returns>
        public override IList<Sprint> GetAllFutureSprints()
        {
            return GetAllSprints(true);
        }

        /// <summary>
        /// Get all sprints for specific project.
        /// </summary>
        /// <param name="projectID">Id of project</param>
        public override IList<Sprint> GetAllSprintsForProject(int projectID)
        {
            return GetAllSprints(projectID, -1, -1, -1);
        }

        /// <summary>
        /// Get all sprints for specific release.
        /// </summary>
        /// <param name="releaseID">Id of release</param>
        public override IList<Sprint> GetAllSprintsForRelease(int releaseID)
        {
            return GetAllSprints(-1, releaseID, -1, -1);
        }

        /// <summary>
        ///  Returns sprint according to id
        /// </summary>
        /// <param name="sprintId">id of sprint</param>
        /// <returns>sprint details</returns>
        public override Sprint GetSprintById(int sprintId)
        {
            IList<Sprint> sprints = GetAllSprints(-1, -1, sprintId, -1);
            if (sprints.Count != 1)
            {
                throw new InconsistentDatabaseException("SprintNotFound");
            }
            return sprints[0];
        }

        /// <summary>
        /// Gets the sprint by userStoryID.
        /// </summary>
        /// <param name="userStoryID">ID of the UserStory.</param>
        /// <returns>sprint containing that user story</returns>
        public override Sprint GetSprintByUserStoryID(int userStoryID)
        {
            IList<Sprint> sprints = GetAllSprints(-1, -1, -1, userStoryID);
            if (sprints.Count != 1)
            {
                throw new InconsistentDatabaseException("SprintNotFound");
            }
            return sprints[0];
        }
    }
}
